package com.Blog.www.utils;


import com.Blog.www.po.*;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * sql的操作
 *
 * @author 君未洋
 */
public class SqlDao {
    //主要的方法有4种,增删查改,查询是个小问题

    private static Connection connection = null;
    private static ResultSet resultSet = null;
    private static PreparedStatement preparedStatement = null;

    /**
     * 实验用操作
     *
     * @return
     */
    public <T> List<T> select(String sql, Class<? extends Connection> aClass, Object... obj) {
        //通过class类来获取方法
        try {
            connection = JDBCUtil.getConnection();
            //执行sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {

            }
        } catch (SQLException throwable) {
            System.out.println(throwable);
            return null;
        }
        return null;
    }

    /**
     * 查询用,获取专栏的封装
     */
    public List<Column> selectColumn(String sql, Object... obj) {
        try {
            List<Column> list = new ArrayList<>();
            connection = JDBCUtil.getConnection();
            //执行sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Column column = new Column();
                column.setId(resultSet.getInt("id"));
                column.setName(resultSet.getString("name"));
                column.setPrior(resultSet.getInt("prior"));
                list.add(column);
            }
            return list;
        } catch (SQLException throwable) {
            System.out.println(throwable);
            return null;
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
    }

    /**
     * 查询用,获取用户收藏类的封装
     */
    public List<UserLike> selectLike(String sql, Object... obj) {
        try {
            List<UserLike> list = new ArrayList<>();
            connection = JDBCUtil.getConnection();
            //执行sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                UserLike like = new UserLike();
                like.setId(resultSet.getInt("id"));
                like.setBlogid(resultSet.getInt("blogid"));
                like.setUserid(resultSet.getInt("userid"));
                list.add(like);
            }
            return list;
        } catch (SQLException throwable) {
            System.out.println(throwable);
            return null;
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
    }

    /**
     * 查询用户与收藏的关系的,有这个收藏就返回一个大于0的数
     */
    public Integer selectUserLike(String sql, Object... obj) {
        try {
            connection = JDBCUtil.getConnection();
            //执行sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                return resultSet.getInt("count");
            }
            return 0;
        } catch (SQLException throwable) {
            System.out.println(throwable);
            return null;
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
    }

    /**
     * 查询用,查询账号用
     */
    public Integer selectAccount(String sql, Object... obj) {
        try {
            connection = JDBCUtil.getConnection();
            //执行sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                return resultSet.getInt("account");
            }
            return 0;
        } catch (SQLException throwable) {
            System.out.println(throwable);
            return null;
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
    }


    /**
     * 查询用,查询总数目
     */
    public int selectCount(String sql, Object... obj) {
        try {
            connection = JDBCUtil.getConnection();
            //执行sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                return resultSet.getInt("count");
            }
            return 0;
        } catch (SQLException throwable) {
            System.out.println(throwable);
            return 0;
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
    }
    /**
     * 搜索并封装对象用
     */
    public List<User> selectAllUser(String sql, Object... obj) {
        try {
            List<User> list = new ArrayList<>();
            connection = JDBCUtil.getConnection();
            //执行sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                User user = new User();
                user.setUsername(resultSet.getString("username"));
                user.setAccount(resultSet.getInt("account"));
                user.setPassword(resultSet.getString("password"));
                user.setId(resultSet.getInt("id"));
                user.setStatus(resultSet.getString("status"));
                user.setIdentity(resultSet.getString("identity"));
                user.setPath(resultSet.getString("path"));
                user.setLastTime( resultSet.getLong("lasttime"));
                list.add(user);
            }
            return list;
        } catch (SQLException throwable) {
            System.out.println(throwable);
            return null;
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
    }

    /**
     * 获取单个用户的获取方法
     */
    public User selectUser(String sql, Object... obj) {
        try {
            List<User> list = new ArrayList<>();
            connection = JDBCUtil.getConnection();
            //执行sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                User user = new User();
                user.setUsername(resultSet.getString("username"));
                user.setAccount(resultSet.getInt("account"));
                user.setPassword(resultSet.getString("password"));
                user.setId(resultSet.getInt("id"));
                user.setStatus(resultSet.getString("status"));
                user.setIdentity(resultSet.getString("identity"));
                user.setPath(resultSet.getString("path"));
                user.setLastTime( resultSet.getLong("lasttime"));
                list.add(user);
            }
            if (list == null || list.size() == 0) {
                return null;
            } else {
                return list.get(0);
            }
        } catch (SQLException throwable) {
            System.out.println(throwable);
            return null;
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
    }

    /**
     * 获取博客的方法
     */
    public UserBlog selectBlog(String sql, Object... obj) {
        try {
            List<UserBlog> list = new ArrayList<>();
            connection = JDBCUtil.getConnection();
            //执行sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                UserBlog blog = new UserBlog();
                blog.setUid(resultSet.getInt("uid"));
                blog.setId(resultSet.getInt("id"));
                blog.setStatus(resultSet.getString("status"));
                blog.setContent(resultSet.getString("content"));
                blog.setTitle(resultSet.getString("title"));
                list.add(blog);
            }
            if (list == null || list.size() == 0) {
                return null;
            } else {
                return list.get(0);
            }
        } catch (SQLException throwable) {
            System.out.println(throwable);
            return null;
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
    }

    /**
     * 执行方法
     */
    public Boolean update(String sql, Object... obj) {
        try {
            connection = JDBCUtil.getConnection();
            //执行sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            int count = preparedStatement.executeUpdate();
            return true;
        } catch (SQLException throwable) {
            System.out.println(throwable);
            return false;
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
    }



    /**
     * 获取用户博客的方法
     */
    public List<UserBlog> selectAllBlog(String sql, Object... obj) {
        try {
            List<UserBlog> list = new ArrayList<>();
            connection = JDBCUtil.getConnection();
            //执行sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                UserBlog blog = new UserBlog();
                blog.setUid(resultSet.getInt("uid"));
                blog.setId(resultSet.getInt("id"));
                blog.setStatus(resultSet.getString("status"));
                blog.setContent(resultSet.getString("content"));
                blog.setTitle(resultSet.getString("title"));
                list.add(blog);
            }
            return list;
        } catch (SQLException throwable) {
            System.out.println(throwable);
            return null;
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
    }

    /**
     * 获取变量名用方法
     */
    public List<String> getName(Object obj) {
        List<String> list = new ArrayList<>();
        Field[] fields = obj.getClass().getDeclaredFields();
        for (Field field : fields) {
            list.add(field.getName());
        }
        return list;
    }

    /**
     * 获取博客评论的封装类
     */
    public List<BlogComment> selectAllComment(String sql, Object... obj) {
        try {
            List<BlogComment> list = new ArrayList<>();
            connection = JDBCUtil.getConnection();
            //执行sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                BlogComment comment = new BlogComment();
                comment.setBlogid(resultSet.getInt("blogid"));
                comment.setContent(resultSet.getString("content"));
                comment.setUserid(resultSet.getInt("userid"));
                comment.setId(resultSet.getInt("id"));
                comment.setPrior(resultSet.getInt("prior"));
                list.add(comment);
            }
            return list;
        } catch (SQLException throwable) {
            System.out.println(throwable);
            return null;
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
    }

    public List<BlogImg> selectAllBlogImg(String sql, Object... obj) {
        try {
            List<BlogImg> list = new ArrayList<>();
            connection = JDBCUtil.getConnection();
            //执行sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                BlogImg img = new BlogImg();
                img.setId(resultSet.getInt("id"));
                img.setBid(resultSet.getInt("bid"));
                img.setPath(resultSet.getString("path"));
                list.add(img);
            }
            return list;
        } catch (SQLException throwable) {
            System.out.println(throwable);
            return null;
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
    }


    /**
     * 搜索并封装对象用
     */
    public List<Label> selectAllLabel(String sql, Object... obj) {
        try {
            List<Label> list = new ArrayList<>();
            connection = JDBCUtil.getConnection();
            //执行sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Label label = new Label();
                label.setId(resultSet.getInt("id"));
                label.setUserId(resultSet.getInt("user_id"));
                label.setContent(resultSet.getString("content"));
                label.setPrior(resultSet.getInt("prior"));
                list.add(label);
            }
            return list;
        } catch (SQLException throwable) {
            System.out.println(throwable);
            return null;
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
    }

    /**
     * 获取数据库中对应的所有的博客id
     *
     * @param sql sql语句
     * @return 返回数据集合
     */
    public List<String> selectAllLabelBid(String sql, Object... obj) {
        try {
            List<String> list = new ArrayList<>();
            connection = JDBCUtil.getConnection();
            //执行sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(resultSet.getString("bid"));
            }
            return list;
        } catch (SQLException throwable) {
            System.out.println(throwable);
            return null;
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
    }
}
